package com.neusoft.elm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import com.neusoft.elm.po.Business;
import com.neusoft.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao  {
	private Connection con=null;
	private PreparedStatement pst=null;
	private ResultSet rs=null;//数据集
	//声明 增删改查 供Dao层方法使用

	@Override
	public List<Business> listBusiness(String businessName, String businessAddress) {
		List<Business> list=new ArrayList<>();//实例化集合，若对象，则为空指针
		StringBuffer sql=new StringBuffer("select * from business where 1=1");
		if(businessName!=null&&!businessName.equals("")) {
			 sql.append(" and businessName like '%"+businessName+"%' ");
		}
		if(businessAddress!=null&&!businessAddress.equals("")) {
			 sql.append(" and businessAddress like '%"+businessAddress+"%' ");
	    }
		try {
			con=DBUtil.getConnection();//获取数据库连接
			pst=con.prepareStatement(sql.toString());//获取执行SQL对象
		    rs = pst.executeQuery();//结果
		    while(rs.next()) {
		    	//把数据库的表数据转换成java对象数据
		    	//封装成一个个商家对象
		    	Business business=new Business();
		    	business.setBusinessId(rs.getInt("businessId"));
		    	business.setPassword(rs.getString("password"));
		    	business.setBusinessName(rs.getString("businessName"));
		    	business.setBusinessAddress(rs.getString("businessAddress"));
		    	business.setBusinessExplain(rs.getString("businessExplain"));
		    	business.setStarPrice(rs.getDouble("starPrice"));
		    	business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
		    	list.add(business);

		    }
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			//释放资源
			DBUtil.close(rs, pst, con);
		}
		return list;
	}

	@Override
	public int createBusiness(String businessName) {
		int businessID=0;//实例化集合，若对象，则为空指针
		String sql="insert into business (businessName,password) values(?,'123');";
		
		try {
			con=DBUtil.getConnection();//获取数据库连接
			pst=con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);//获取执行SQL对象，并获取自增的主键
			pst.setString(1,businessName);
		     pst.executeUpdate();//更新操作结果
		     rs=pst.getGeneratedKeys();//获取自增长的主键（一行一列）
		    while(rs.next()) {
		    	//把数据库的表数据转换成java对象数据
		    	businessID=rs.getInt(1);
		    	//可以通过索引或者列名来获得查询结果集中的某一列的值。	
		    }
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			//释放资源
			DBUtil.close(rs, pst, con);
		}
		return businessID; 
	}

	@Override
	public int removeBusiness(int businessId) {//有主外键关联，先删子表，再删主表
		int result=0;
		String delfood_sql="delete from food where businessId=? ";
		String delbusiness_sql="delete from business where businessId=? ";
		//为了保持数据完整性，两个操作要么都成功，要么都失败；开启JDBC事务
		
		try {
			con=DBUtil.getConnection();//获取数据库连接
			
			con.setAutoCommit(false);//不自动提交事务，即开启一个事务
			
			pst=con.prepareStatement(delfood_sql);
			pst.setInt(1,businessId);
		    pst.executeUpdate();//更新操作结果
		    
		    pst=con.prepareStatement(delbusiness_sql);
			pst.setInt(1,businessId);
		    result=pst.executeUpdate();//更新操作结果
		    
		    con.commit();//两步放在同一个事务中，一起提交
		    
		    //回滚 判断每个事务的成功性 都成功了 catch不会执行
		}catch(SQLException e) {
			result=0;
			try{
				con.rollback();
			}catch(SQLException e1){
				e1.printStackTrace();	
			}
			e.printStackTrace();
		}finally {
			//释放资源
			DBUtil.close(null, pst, con);
		}
		return result; 
	}

	@Override
	public Business getBusinessByTdByPass(Integer businessId, String password) {
		Business business=null;//指向空指针，最后查不出数据，则为空指针
		String sql="select * from business where businessId=? and password=?";
		try {
			con=DBUtil.getConnection();//获取数据库连接
			pst=con.prepareStatement(sql);//获取执行SQL对象
		    pst.setInt(1,businessId);
		    pst.setString(2,password);
		    rs = pst.executeQuery();
		    if(rs.next()) {
		    	//把数据库的表数据转换成java对象数据
		    	business=new Business();
		    	business.setBusinessId(rs.getInt("businessId"));
		    	business.setPassword(rs.getString("password"));
		    	business.setBusinessName(rs.getString("businessName"));
		    	business.setBusinessAddress(rs.getString("businessAddress"));
		    	business.setBusinessExplain(rs.getString("businessExplain"));
		    	business.setStarPrice(rs.getDouble("starPrice"));
		    	business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
		    }
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			//释放资源
			DBUtil.close(rs, pst, con);
		}
		
		return business;
	}

	@Override
	public Business getBusinessByTd(int businessId) {
		Business business=null;
		String sql="select * from business where businessId=?";
		try{
			con=DBUtil.getConnection();
			pst=con.prepareStatement(sql);
			pst.setInt(1, businessId);
			rs=pst.executeQuery();
			
			if(rs.next()) {
				business=new Business();
				business.setBusinessId(rs.getInt("businessId"));
		    	business.setPassword(rs.getString("password"));
		    	business.setBusinessName(rs.getString("businessName"));
		    	business.setBusinessAddress(rs.getString("businessAddress"));
		    	business.setBusinessExplain(rs.getString("businessExplain"));
		    	business.setStarPrice(rs.getDouble("starPrice"));
		    	business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, pst, con);
		}
		return business;		
	}

	@Override
	public int updateBusiness(Business business) {
		int result=0;
		String sql="update business set businessName=?,businessAddress=?,businessExplain=?,starPrice=?,deliveryPrice=? where businessId=?";

		
		try {
			con=DBUtil.getConnection();//获取数据库连接
			pst=con.prepareStatement(sql);
			
			pst.setString(1,business.getBusinessName());
			pst.setString(2,business.getBusinessAddress());
			pst.setString(3,business.getBusinessExplain());
			pst.setDouble(4,business.getStarPrice());
			pst.setDouble(5,business.getDeliveryPrice());
			pst.setInt(6,business.getBusinessId());
			
		    result=pst.executeUpdate();//更新操作结果
		    
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			//释放资源
			DBUtil.close(null, pst, con);
		}
		return result; 
	}

	@Override
	public int updateBusinessPassword(Integer businessId, String password) {
		int result=0;
		String sql="update business set password=? where businessId=?";
		try {
			con=DBUtil.getConnection();
			pst=con.prepareStatement(sql);
			pst.setString(1,password);
			pst.setInt(2, businessId);
			
			result=pst.executeUpdate();
			
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			//释放资源
			DBUtil.close(null, pst, con);
		}
		return result; 
	}
		
}